package it.controllocredito.backend.persistence;

import java.util.ArrayList;
import java.util.List;

import it.controllocredito.backend.persistence.bean.Configuration;
import it.controllocredito.backend.persistence.bean.Monitor;
import it.controllocredito.backend.persistence.bean.RegistroChiamate;
import it.controllocredito.backend.persistence.bean.Soglia;
import it.controllocredito.backend.persistence.bean.Soglia.TipoEnum;
import it.controllocredito.backend.persistence.tables.ConfigurationTable;
import it.controllocredito.backend.persistence.tables.MonitorTable;
import it.controllocredito.backend.persistence.tables.RegistroChiamateTable;
import it.controllocredito.backend.persistence.tables.RegistroChiamateTable.Columns;
import it.controllocredito.backend.persistence.tables.SogliaTable;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.CallLog;

public class DatabaseHandler extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "CONTROLLO_CREDITO_DB";

    public DatabaseHandler(Context context) {
	super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    public void initDB() {
	this.onCreateFull(this.getWritableDatabase());
    }

    // Crea le tabelle. Questo metodo va invocato solamente la prima volta che
    // viene lanciata l'app
    @Override
    public void onCreate(SQLiteDatabase db) {
	db.execSQL(RegistroChiamateTable.getCreateQuery());
	// db.execSQL(MonitorTable.getCreateQuery());
    }

    public void onCreateFull(SQLiteDatabase db) {
	this.onCreate(db);
	db.execSQL(ConfigurationTable.getCreateQuery());
	db.execSQL(SogliaTable.getCreateQuery());
	// db.execSQL(MonitorTable.getCreateQuery());
    }

    public long addRecord(String tableName, ContentValues cv) {
	long rowId = -1;
	SQLiteDatabase db = this.getWritableDatabase();
	try {
	    rowId = db.insert(tableName, null, cv);
	    db.close();
	} catch (Exception ex) {
	    rowId = -1;
	    ex.printStackTrace();
	}
	return rowId;
    }

    public boolean updateRecord(String tableName, ContentValues cv, String whereClause, String[] whereArgs) {
	boolean recordAdded = false;
	SQLiteDatabase db = this.getWritableDatabase();
	try {
	    db.update(tableName, cv, whereClause, whereArgs);
	    db.close();
	    recordAdded = true;
	} catch (Exception ex) {
	    recordAdded = false;
	    ex.printStackTrace();
	}
	return recordAdded;
    }

    // registro_chiamate

    public RegistroChiamate getRecordRegistroChiamate(int id) {
	SQLiteDatabase db = this.getReadableDatabase();

	Cursor cursor = db.query(RegistroChiamateTable.getName(), RegistroChiamateTable.getColumns(), RegistroChiamateTable.getKeyColumn() + "=?",
		new String[] { String.valueOf(id) }, null, null, null, null);
	if (cursor != null)
	    cursor.moveToFirst();

	RegistroChiamate registroChiamate = new RegistroChiamate();
	db.close();
	return registroChiamate;
    }

    public List<RegistroChiamate> getRecordsRegistroChiamate() {
	String sql = "SELECT  * FROM " + RegistroChiamateTable.getName() + " ORDER BY " + Columns.ANNO.toString() + " DESC," + Columns.MESE.toString() + " DESC,"
		+ Columns.GIORNO.toString() + " DESC," + Columns.ORA.toString() + " DESC";

	return recordsRegistroChiamate(sql);
    }
    
    public List<RegistroChiamate> getBlockRecordsRegistroChiamate(int block, int limit) {
    	String sql = "SELECT  * FROM " + RegistroChiamateTable.getName() + " ORDER BY " + Columns.ANNO.toString() + " DESC," + Columns.MESE.toString() + " DESC,"
    		+ Columns.GIORNO.toString() + " DESC," + Columns.ORA.toString() + " DESC LIMIT " + ((block-1)*limit) + ", " + limit;

    	return recordsRegistroChiamate(sql);
    }
    
    public int getTotalRecordsRegistroChiamate() {
    	String sql = "SELECT  count(*) FROM " + RegistroChiamateTable.getName() + " ORDER BY " + Columns.ANNO.toString() + " DESC," + Columns.MESE.toString() + " DESC,"
    		+ Columns.GIORNO.toString() + " DESC," + Columns.ORA.toString() + " DESC ";

    	return totalRecordsRegistroChiamate(sql);
    }
    
    public int getTotalTimeOutgoingRegistroChiamate() {
    	String sql = "SELECT  sum(" + Columns.DURATA.toString() + ") FROM " + RegistroChiamateTable.getName() + " WHERE " + Columns.TIPO.toString() + "=" + CallLog.Calls.OUTGOING_TYPE;
    	return totalRecordsRegistroChiamate(sql);
    }

    public List<RegistroChiamate> getRecordsRegistroChiamate(String sqlWehereCondition) {
	String sql = "SELECT  * FROM " + RegistroChiamateTable.getName() + " WHERE " + sqlWehereCondition + " ORDER BY " + Columns.ANNO.toString() + " DESC,"
		+ Columns.MESE.toString() + " DESC," + Columns.GIORNO.toString() + " DESC," + Columns.ORA.toString() + " DESC";

	return recordsRegistroChiamate(sql);
    }

    private List<RegistroChiamate> recordsRegistroChiamate(String selectQuery) {
	List<RegistroChiamate> registroChiamateList = new ArrayList<RegistroChiamate>();

	SQLiteDatabase db = this.getWritableDatabase();
	Cursor cursor = db.rawQuery(selectQuery, null);

	// looping through all rows and adding to list
	if (cursor.moveToFirst()) {
	    do {
		registroChiamateList.add(RegistroChiamateTable.getRecord(cursor));
	    } while (cursor.moveToNext());
	}

	db.close();

	return registroChiamateList;
    }
    
    private int totalRecordsRegistroChiamate(String selectQuery) {
    	int totalCalls=0;
    	SQLiteDatabase db = this.getWritableDatabase();
    	Cursor cursor = db.rawQuery(selectQuery, null);

    	// looping through all rows and adding to list
    	if (cursor.moveToFirst()) {
    		totalCalls = cursor.getInt(0);
    	}

    	db.close();

    	return totalCalls;
    }

    // monitor

    public Monitor getRecordMonitor(int id) {
	SQLiteDatabase db = this.getReadableDatabase();

	Cursor cursor = db
		.query(MonitorTable.getName(), MonitorTable.getColumns(), MonitorTable.getKeyColumn() + "=?", new String[] { String.valueOf(id) }, null, null, null, null);
	if (cursor != null)
	    cursor.moveToFirst();

	Monitor monitor = new Monitor();
	db.close();
	return monitor;
    }

    public List<Monitor> getAllRecordsMonitor() {
	List<Monitor> monitorList = new ArrayList<Monitor>();
	// Select All Query
	String selectQuery = "SELECT  * FROM " + MonitorTable.getName();

	SQLiteDatabase db = this.getWritableDatabase();
	Cursor cursor = db.rawQuery(selectQuery, null);

	// looping through all rows and adding to list
	if (cursor.moveToFirst()) {
	    do {
		// Adding contact to list
		monitorList.add(MonitorTable.getRecord(cursor));
	    } while (cursor.moveToNext());
	}

	db.close();
	// return contact list
	return monitorList;
    }

    public Soglia getSoglia(long id) {
	SQLiteDatabase db = this.getReadableDatabase();

	Cursor cursor = db.rawQuery("SELECT * FROM " + SogliaTable.getName() + " WHERE " + SogliaTable.Columns.SOGLIA_ID.toString() + " = " + id + ";", null);

	Soglia soglia = null;
	if (cursor != null) {
	    if (cursor.moveToFirst()) {
		soglia = this.populateSoglia(cursor);
	    }
	    cursor.close();
	}
	db.close();
	return soglia;
    }

    public Configuration getConfiguration(long id) {
	SQLiteDatabase db = this.getReadableDatabase();

	Cursor cursor = db.rawQuery("SELECT * FROM " + ConfigurationTable.getName() + " WHERE " + ConfigurationTable.Columns.CONFIGURATION_ID.toString() + " = " + id + ";", null);

	Configuration configuration = null;
	if (cursor != null) {
	    if (cursor.moveToFirst()) {
		configuration = this.populateConfiguration(cursor, this.getSoglieByConfigurationId(id));
	    }
	    cursor.close();
	}
	db.close();
	return configuration;
    }

//    public Configuration getConfigurationByName(String configurationName) {
//	SQLiteDatabase db = this.getReadableDatabase();
//
//	Cursor cursor = db.rawQuery("SELECT * FROM " + ConfigurationTable.getName() + " WHERE " + ConfigurationTable.Columns.NOME.toString() + " = '" + configurationName + "';",
//		null);
//
//	Configuration configuration = new Configuration();
//	if (cursor != null) {
//	    if (cursor.moveToFirst()) {
//		configuration = this.populateConfiguration(cursor, getSoglie);
//	    }
//	    cursor.close();
//	}
//	db.close();
//	return configuration;
//    }

    public ArrayList<Soglia> getSoglieByConfigurationId(long configurationId){
	SQLiteDatabase db = this.getReadableDatabase();

	ArrayList<Soglia> soglie = null;
	
	Cursor cursor = db.rawQuery("SELECT * FROM " + SogliaTable.getName() + " WHERE " + SogliaTable.Columns.CONFIGURATION_ID.toString() + " = " + configurationId + ";",
		null);

	if (cursor != null) {
	    soglie = new ArrayList<Soglia>();
	    while (cursor.moveToNext()) {
		soglie.add(this.populateSoglia(cursor));
	    }
	}
	return soglie;
    }
    
    public ArrayList<Soglia> getRecordsSoglia(TipoEnum tipoSoglia){
    	SQLiteDatabase db = this.getReadableDatabase();

    	ArrayList<Soglia> soglie = null;
    	
    	Cursor cursor = db.rawQuery("SELECT * FROM " + SogliaTable.getName() + " WHERE " + SogliaTable.Columns.TIPO.toString() + " = '" + tipoSoglia + "';",
    		null);

    	if (cursor != null) {
    	    soglie = new ArrayList<Soglia>();
    	    while (cursor.moveToNext()) {
    		soglie.add(this.populateSoglia(cursor));
    	    }
    	}
    	return soglie;
    }
    
    public void dropTable(String tableName) {
	SQLiteDatabase db = this.getWritableDatabase();
	db.execSQL("DROP TABLE IF EXISTS '" + tableName + "'");
	onCreate(db);

    }

    public void dropOnlyTable(String tableName) {
	SQLiteDatabase db = this.getWritableDatabase();
	db.execSQL("DROP TABLE IF EXISTS '" + tableName + "'");
	db.close();
    }

    public boolean deleteAllRecords(String tableName) {
	boolean allRecordsDeleted = false;

	SQLiteDatabase db = this.getWritableDatabase();
	try {
	    db.delete(tableName, null, null);
	    db.close();
	    allRecordsDeleted = true;
	} catch (Exception ex) {
	    allRecordsDeleted = false;
	    ex.printStackTrace();
	}
	return allRecordsDeleted;
    }
    
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	// TODO Auto-generated method stub
    }
    
    private Configuration populateConfiguration(Cursor cursor, ArrayList<Soglia> soglie){ 
  	Configuration configuration = new Configuration();
  	configuration.setConfigurationId(cursor.getInt(cursor.getColumnIndex(ConfigurationTable.Columns.CONFIGURATION_ID.toString())));
  	configuration.setNome(cursor.getString(cursor.getColumnIndex(ConfigurationTable.Columns.NOME.toString())));
  	configuration.setAutore(cursor.getString(cursor.getColumnIndex(ConfigurationTable.Columns.AUTORE.toString())));
  	configuration.setDataCreazioneString(cursor.getString(cursor.getColumnIndex(ConfigurationTable.Columns.DATA_CREAZIONE.toString())));
  	configuration.setVersione(cursor.getString(cursor.getColumnIndex(ConfigurationTable.Columns.VERSIONE.toString())));
  	configuration.setSoglie(soglie);
  	return configuration;
      }
    
    private Soglia populateSoglia(Cursor cursor){
	Soglia soglia = new Soglia();
	soglia.setSogliaId(cursor.getInt(cursor.getColumnIndex(SogliaTable.Columns.SOGLIA_ID.toString())));
	soglia.setTipoString(cursor.getString(cursor.getColumnIndex(SogliaTable.Columns.TIPO.toString())));
	soglia.setFrequenzaString(cursor.getString(cursor.getColumnIndex(SogliaTable.Columns.FREQUENZA.toString())));
	soglia.setGiornoMeseInizio(cursor.getInt(cursor.getColumnIndex(SogliaTable.Columns.GIORNO_INIZIO_MESE.toString())));
	soglia.setGiornoSettimanaInizioString(cursor.getString(cursor.getColumnIndex(SogliaTable.Columns.GIORNO_INIZIO_SETTIMANA.toString())));
	soglia.setUnitaDiMisuraString(cursor.getString(cursor.getColumnIndex(SogliaTable.Columns.UNITA_DI_MISURA.toString())));
	soglia.setValore(cursor.getInt(cursor.getColumnIndex(SogliaTable.Columns.VALORE.toString())));
	soglia.setConfigurationId(cursor.getInt(cursor.getColumnIndex(SogliaTable.Columns.CONFIGURATION_ID.toString())));
	return soglia;
    }
}